//
//  SlapperController.m
//  My Dream Slapper
//
//  Created by Jason Harris on 8/22/06.
//  Copyright (c) 2006, Jason Harris and Geekspiff
//  All rights reserved.
//  
//  Redistribution and use in source and binary forms, with or without modification, are permitted provided
//  that the following conditions are met:
//  
//  Redistributions of source code must retain the above copyright notice, this list of conditions and the
//  following disclaimer. Redistributions in binary form must reproduce the above copyright notice, this list
//  of conditions and the following disclaimer in the documentation and/or other materials provided with the
//  distribution. Neither the name of Jason Harris, Geekspiff, nor the names of its contributors may be used
//  to endorse or promote products derived from this software without specific prior written permission.
//  
//  THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED
//  WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A
//  PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY
//  DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
//  PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
//  HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
//  NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
//  POSSIBILITY OF SUCH DAMAGE.
//

#import "SlapperController.h"
#import "FolderCell.h"
#import "SlapperPreferences.h"
#import "CoreData/Folder.h"
#import "CoreData/MyManagedObjectContext.h"


static NSString *kSubmissionPasteboardType = @"com.geekspiff.MyDreamSlapper.submission";


@implementation SlapperController

#pragma mark Database Helpers

- (NSTimeZone *)_databaseTimezone
{
	if ( ! _dbTimezone )
	{
		_dbTimezone = [[NSTimeZone timeZoneWithName: @"EST"] retain];
		NSParameterAssert( _dbTimezone != nil );
	}
	return _dbTimezone;
}


- (NSString *)_dateForDatabase: (NSDate *)date
{
	NSCalendarDate *cd = [NSCalendarDate dateWithString: [date description] calendarFormat: @"%Y-%m-%d %H:%M:%S %z"];
	[cd setTimeZone: [self _databaseTimezone]];
	return [_connection quoteObject: cd];
}


- (NSCalendarDate *)_dateFromDatabase: (NSCalendarDate *)date
{
	NSCalendarDate *cd = [[date copy] autorelease];
	[cd setTimeZone: [NSTimeZone defaultTimeZone]];
	return cd;
}


- (unsigned int)_databaseTimeIntervalForDate: (NSDate *)date
{
	NSCalendarDate *cd = [NSCalendarDate dateWithString: [date description] calendarFormat: @"%Y-%m-%d %H:%M:%S %z"];
	[cd setTimeZone: [self _databaseTimezone]];
	return (unsigned int)[cd timeIntervalSince1970];
}


#pragma mark Creation and Destruction


+ (void)initialize
{
	// make sure our pref defaults are registered
	[SlapperPreferences class];

	[self exposeBinding: @"isFolderNameValid"];
	[self setKeys: [NSArray arrayWithObject: @"newFolderName"] triggerChangeNotificationsForDependentKey: @"isFolderNameValid"];
}


- (id)initWithWindow:(NSWindow *)window
{
	if (self = [super initWithWindow: window])
	{
		_newComment = [[NSData alloc] init];
		[self initCoreData];
		[self initMetadataStore];

		NSSortDescriptor *sortDescriptor1, *sortDescriptor2;
        sortDescriptor1 = [[[NSSortDescriptor alloc] initWithKey: @"sortKey" ascending: YES] autorelease];
        sortDescriptor2 = [[[NSSortDescriptor alloc] initWithKey: @"name" ascending: YES] autorelease];
		_folderSortDescriptors = [[NSArray alloc] initWithObjects: sortDescriptor1, sortDescriptor2, nil];
		[self createSortDescriptorsFromPrefs];
	}
	return self;
}


- (void)dealloc
{
	[[NSNotificationCenter defaultCenter] removeObserver: self];
	[_managedObjectContext release];
	[_persistentStoreCoordinator release];
	[_connection release];
	[_password release];
	[_connectionError release];
	[_isConnecting release];
	[_status release];
	[_statusColor release];
	[_newComment release];
	[self stopSynchronizing];
	[_folderSortDescriptors release];
	[_sortDescriptors release];
	[_commentsSortDescriptors release];
	[_newFolderName release];
	[_dbTimezone release];
 	[super dealloc];
}


- (void)awakeFromNib
{
	[[NSNotificationCenter defaultCenter] addObserver: self 
											 selector: @selector(applicationWillTerminate:) 
												 name: NSApplicationWillTerminateNotification 
											   object: nil];
	
	[_connectionProgressSpinner setUsesThreadedAnimation: YES];
	
	[_foldersTableView registerForDraggedTypes: [NSArray arrayWithObject: kSubmissionPasteboardType]];
	[_submissionsTableView setDraggingSourceOperationMask: NSDragOperationCopy forLocal: YES];

	FolderCell *folderCell = [[[FolderCell alloc] init] autorelease];
	[[_foldersTableView tableColumnWithIdentifier: @"folder"] setDataCell: folderCell];
	
	NSString *path = [[NSBundle mainBundle] pathForResource: @"unread_header" ofType: @"tiff"];
	NSImage *headerImage = [[[NSImage alloc] initWithContentsOfFile: path] autorelease];
	[[[_submissionsTableView tableColumnWithIdentifier: @"read"] headerCell] setImage: headerImage];

	[_submissionsController addObserver: self forKeyPath: @"selectedObjects" options: NSKeyValueObservingOptionNew context: @"SubmissionsSelection"];
	[self initConnection];

	[self performSelector: @selector(_selectPrimaryFolder) withObject: nil afterDelay: 0.1];
}


- (void)_selectPrimaryFolder
{
	[_foldersTableView selectRowIndexes: [NSIndexSet indexSetWithIndex: 0] byExtendingSelection: NO];
}


- (void)applicationWillTerminate: (NSNotification *)notification
{
	[self saveCoreDataStore];
	[self saveSortDescriptorsToPrefs];
}


#pragma mark Connection


- (void)initConnection
{
	NSUserDefaults *ud = [NSUserDefaults standardUserDefaults];
	NSDate *lastDBRead = [ud objectForKey: @"LastDatabaseRead"];
	if ( ! lastDBRead )
		[self createConnection: nil];
	[self setupAutoSyncing];
}


- (void)setupAutoSyncing
{
	NSUserDefaults *ud = [NSUserDefaults standardUserDefaults];
	[self stopSynchronizing];
	if ( [ud boolForKey: @"SynchronizeAutomatically"] )
	{
		NSTimeInterval timeInterval = [ud floatForKey: @"SynchronizeMinutes"] * 60 * 60;
		_synchronizeTimer = [NSTimer scheduledTimerWithTimeInterval: timeInterval target: self selector: @selector(synchronize:) userInfo: nil repeats: YES];
		[self synchronize: nil];
	}
}


- (IBAction)createConnection: (id)sender
{
	[self setStatus: @"" kind: kSlapperNormalStatus];
	if (_connection)
	{
		[self setStatus: NSLocalizedString(@"LoggedIn", nil) kind: kSlapperNormalStatus];
		return;
	}
	if ([self username] && _password)
		if ([self login]) return;
	[self presentConnectionSheet];
}


- (NSString *)username
{
	return [[[NSUserDefaultsController sharedUserDefaultsController] values] valueForKey: @"username"];
}


- (BOOL)login
{
	[_connection release];
	_connection = nil;
	[self setValue: [NSNumber numberWithBool: YES] forKey: @"isConnecting"];
	NSString *username = [self username];
	
	@try
	{
		_connection = [[MCPConnection alloc] initToHost: NSLocalizedString(@"DatabaseHost", nil) withLogin: [self username] password: _password usingPort: 0];
		if (_connection)
		{
			BOOL selectedDB = [_connection selectDB: NSLocalizedString(@"Database", nil)];
			if (!selectedDB)
			{
				[_connection release];
				_connection = nil;
			}
		}
		
		if (!_connection)
		{
			[self setValue: NSLocalizedString(@"badLogin", nil) forKey: @"connectionError"];
			return NO;
		}
	}
	
	@finally
	{
		[self setValue: [NSNumber numberWithBool: NO] forKey: @"isConnecting"];
	}
	
	[[[NSUserDefaultsController sharedUserDefaultsController] values] setValue: username forKey: @"LastValidSQLUsername"];
	[[self managedObjectContext] uncacheSQLUser];
	return YES;
}


- (void)presentConnectionSheet
{
	[self showWindow: nil];
	[self setValue: @"" forKey: @"connectionError"];
	[self setStatus: @"" kind: kSlapperNormalStatus];
	[NSApp beginSheet: _connectionSheet modalForWindow: [self window] modalDelegate: self didEndSelector: @selector(connectionSheetDidEnd:returnCode:contextInfo:) contextInfo: nil];
}


- (void)connectionSheetDidEnd: (NSWindow *)sheet returnCode: (int)returnCode contextInfo: (void *)contextInfo
{
	[sheet orderOut: nil];
	switch (returnCode)
	{
		case NSOKButton:
			[self loggedInSynchronize];
			break;
		case NSCancelButton:
			break;
	}
}


- (IBAction)connectionLoginPressed: (NSButton *)sender
{
	BOOL loggedIn = [self login];
	if ( loggedIn )
		[NSApp endSheet: _connectionSheet returnCode: NSOKButton];
}


- (IBAction)connectionCancelPressed: (NSButton *)sender
{  [NSApp endSheet: _connectionSheet returnCode: NSCancelButton];  }


#pragma mark Synchronizing


- (IBAction)synchronize: (NSTimer *)timer
{
	[self setStatus: @"" kind: kSlapperNormalStatus];
	if ( [_connectionSheet isVisible] )
		return;
	
	if ( ! _connection )
		[self presentConnectionSheet];
	if ( ! _connection )
	{
		[self setStatus: NSLocalizedString(@"NoSynchronizeLogin", nil) kind: kSlapperErrorStatus];
		return;
	}
	else
		[self loggedInSynchronize];
}


- (void)loggedInSynchronize
{
	[self setStatus: NSLocalizedString(@"SynchronizeUpdateModified", nil) kind: kSlapperNormalStatus];
	[self performSelector: @selector(performSynchronizationActions) withObject: nil afterDelay: 0.1];
}


- (void)performSynchronizationActions
{
	[self syncSQLUsers];
	[self syncSharedFolders];
	[self syncContestEntries];
	[self syncEntryComments];
	[self syncRatings];
	[self syncSharedFolderSubmissions];

	[self uploadDirtyObjects];
	
	[[NSUserDefaults standardUserDefaults] setObject: [NSDate date] forKey: @"LastDatabaseRead"];
	[self saveCoreDataStore];
	[self setStatus: @"" kind: kSlapperNormalStatus];
}


- (void)stopSynchronizing
{
	[_synchronizeTimer invalidate];
	_synchronizeTimer = nil;
}


#pragma mark CoreData


- (void)initCoreData
{
    // create the model and coordinator
	NSArray *bundles = [NSArray arrayWithObject: [NSBundle mainBundle]];
	NSManagedObjectModel *model = [NSManagedObjectModel mergedModelFromBundles: bundles];
	_persistentStoreCoordinator = [[NSPersistentStoreCoordinator alloc] initWithManagedObjectModel: model];
	
	// create the context and set its coordinator and merge policy
	_managedObjectContext = [[MyManagedObjectContext alloc] init];
	[_managedObjectContext setPersistentStoreCoordinator: _persistentStoreCoordinator];
}


- (void)initMetadataStore
{
	NSFileManager *fm = [NSFileManager defaultManager];
	NSPersistentStoreCoordinator *psc = [self persistentStoreCoordinator];
	NSError *error = nil;
	
	// create the location for the store if necessary	
	FSRef folder;
	OSStatus status;
	NSString *metadataStorePath = nil;
	status = FSFindFolder(kUserDomain, kPreferencesFolderType, true, &folder);
	if (noErr == status)
	{
		CFURLRef url = CFURLCreateFromFSRef(kCFAllocatorDefault, &folder);
		if (url)
		{
			NSString *filename = [NSString stringWithFormat: @"%@.store", [[NSBundle mainBundle] bundleIdentifier]];
			metadataStorePath = [(NSURL *)url path];
			metadataStorePath = [metadataStorePath stringByAppendingPathComponent: filename];
			CFRelease(url);
		}
	}
	NSParameterAssert( metadataStorePath != nil );
	NSURL *metadataStoreURL = [NSURL fileURLWithPath: metadataStorePath];
	
	// if the store already exists, find its store type
	BOOL storeExists = NO;
	NSString *storeType = NSSQLiteStoreType;
	if ([fm fileExistsAtPath: metadataStorePath])
	{
		NSDictionary *storeMetadata = [NSPersistentStoreCoordinator metadataForPersistentStoreWithURL: metadataStoreURL error: &error];
		if ( ! storeMetadata || error )
		{
			NSLog(@"The metadata store is damaged.  It will be automatically regenerated.");
			[fm removeFileAtPath: metadataStorePath handler: nil];
			[[NSUserDefaults standardUserDefaults] removeObjectForKey: @"LastDatabaseRead"];
			error = nil;
		}
		else
		{
			storeType = [storeMetadata valueForKey: NSStoreTypeKey];
			storeExists = YES;
		}
	}
	
	// open the new store
	id newStore = [psc addPersistentStoreWithType: storeType configuration: nil URL: metadataStoreURL options: nil error: &error];
	if ( ! newStore || error )
	{
		[NSException raise: NSGenericException format: @"%@", error];
	}
	
	if ( ! storeExists )
		[self configureNewStore];
}


- (void)configureNewStore
{
	NSManagedObjectContext *moc = [self managedObjectContext];
	NSManagedObject *folder;
	
	folder = [NSEntityDescription insertNewObjectForEntityForName: @"Folder" inManagedObjectContext: moc];
	[folder setValue: NSLocalizedString(@"AllSubmissionsFolder", nil) forKey: @"name"];
	[folder setValue: [NSNumber numberWithBool: NO] forKey: @"canBeDeleted"];
	[folder setValue: [NSNumber numberWithInt: 0] forKey: @"sortKey"];
	[folder setValue: @"TRUEPREDICATE" forKey: @"predicateString"];
	
	folder = [NSEntityDescription insertNewObjectForEntityForName: @"Folder" inManagedObjectContext: moc];
	[folder setValue: NSLocalizedString(@"UnratedFolder", nil) forKey: @"name"];
	[folder setValue: [NSNumber numberWithBool: NO] forKey: @"canBeDeleted"];
	[folder setValue: [NSNumber numberWithInt: 1] forKey: @"sortKey"];
	[folder setValue: @"isRated == NO" forKey: @"predicateString"];
	
	folder = [NSEntityDescription insertNewObjectForEntityForName: @"Folder" inManagedObjectContext: moc];
	[folder setValue: NSLocalizedString(@"RatedFolder", nil) forKey: @"name"];
	[folder setValue: [NSNumber numberWithBool: NO] forKey: @"canBeDeleted"];
	[folder setValue: [NSNumber numberWithInt: 2] forKey: @"sortKey"];
	[folder setValue: @"isRated == YES" forKey: @"predicateString"];
	
	folder = [NSEntityDescription insertNewObjectForEntityForName: @"Folder" inManagedObjectContext: moc];
	[folder setValue: NSLocalizedString(@"GloballyUnratedFolder", nil) forKey: @"name"];
	[folder setValue: [NSNumber numberWithBool: NO] forKey: @"canBeDeleted"];
	[folder setValue: [NSNumber numberWithInt: 3] forKey: @"sortKey"];
	[folder setValue: @"isGloballyRated == NO" forKey: @"predicateString"];
	
	folder = [NSEntityDescription insertNewObjectForEntityForName: @"Folder" inManagedObjectContext: moc];
	[folder setValue: NSLocalizedString(@"GloballyRatedFolder", nil) forKey: @"name"];
	[folder setValue: [NSNumber numberWithBool: NO] forKey: @"canBeDeleted"];
	[folder setValue: [NSNumber numberWithInt: 4] forKey: @"sortKey"];
	[folder setValue: @"isGloballyRated == YES" forKey: @"predicateString"];
	
	folder = [NSEntityDescription insertNewObjectForEntityForName: @"Folder" inManagedObjectContext: moc];
	[folder setValue: NSLocalizedString(@"CommentedFolder", nil) forKey: @"name"];
	[folder setValue: [NSNumber numberWithBool: NO] forKey: @"canBeDeleted"];
	[folder setValue: [NSNumber numberWithInt: 5] forKey: @"sortKey"];
	[folder setValue: @"comments.@count > 0" forKey: @"predicateString"];
	
	folder = [NSEntityDescription insertNewObjectForEntityForName: @"Folder" inManagedObjectContext: moc];
	[folder setValue: NSLocalizedString(@"UncommentedFolder", nil) forKey: @"name"];
	[folder setValue: [NSNumber numberWithBool: NO] forKey: @"canBeDeleted"];
	[folder setValue: [NSNumber numberWithInt: 6] forKey: @"sortKey"];
	[folder setValue: @"comments.@count == 0" forKey: @"predicateString"];
}


- (void)saveCoreDataStore
{
	NSManagedObjectContext *moc = [self managedObjectContext];
	if ([moc hasChanges])
	{
		NSError *error;
		if (![moc save: &error] )
			[NSException raise: NSGenericException format: @"%@", error];
	}
}


- (MyManagedObjectContext *)managedObjectContext
{  return _managedObjectContext;  }


- (NSPersistentStoreCoordinator *)persistentStoreCoordinator
{  return _persistentStoreCoordinator;  }


- (void)syncSQLUsers
{
	NSString *usersTable = NSLocalizedString(@"UsersTable", nil);
	NSString *sqlUsersTable = NSLocalizedString(@"SQLUsersTable", nil);
	NSString *query = [NSString stringWithFormat: @"SELECT %@.*, %@.realname FROM %@, %@ WHERE (%@.user_id = %@.id)", sqlUsersTable, usersTable, sqlUsersTable, usersTable, sqlUsersTable, usersTable];
	MCPResult *result = [_connection queryString: query];
	[self syncSQLUsersWithRows: result];
}


- (void)syncSQLUsersWithRows: (MCPResult *)rows
{
	NSError *error;
	NSManagedObjectContext *moc = [self managedObjectContext];
	NSFetchRequest *fetchRequest = [[[NSFetchRequest alloc] init] autorelease];
	NSEntityDescription *entity = [NSEntityDescription entityForName: @"SQLUser" inManagedObjectContext: moc];
	[fetchRequest setEntity: entity];
	
	NSDictionary *sqlUser;
	while (sqlUser = [rows fetchRowAsDictionary])
	{
		id sqlUserEntity = nil;
		NSNumber *rowID = [sqlUser objectForKey: @"id"];
		
		// is this user already in our Core Data store?
		NSPredicate *predicate = [NSPredicate predicateWithFormat: @"id == %@", rowID];
		[fetchRequest setPredicate: predicate];
		NSArray *foundEntries = [moc executeFetchRequest: fetchRequest error: &error];
		if (!foundEntries && error)
			[NSException raise: NSGenericException format: @"%@", error];
		if (foundEntries)
		{
			unsigned int count = [foundEntries count];
			if ( 0 != count )
			{
				if (1 != [foundEntries count])
					[NSException raise: NSGenericException format: @"a duplicate ID (%@) was found", rowID];
				sqlUserEntity = [foundEntries lastObject];
			}
		}
		
		// and populate the new entity
		if (sqlUserEntity)
			[self updateSQLUser: sqlUserEntity fromDictionary: sqlUser];
		else
			[self createSQLUserFromDictionary: sqlUser];
	}
}


- (void)createSQLUserFromDictionary: (NSDictionary *)srcDict
{
	float r, g, b, a;
	NSColor *color;
	NSData *colorData;
	
	NSManagedObjectContext *moc = [self managedObjectContext];
	id entity = [NSEntityDescription insertNewObjectForEntityForName: @"SQLUser" inManagedObjectContext: moc];
    
	[entity setValue: [srcDict objectForKey: @"id"] forKey: @"id"];
	[entity setValue: [srcDict objectForKey: @"user_id"] forKey: @"userID"];
	[entity setValue: [srcDict objectForKey: @"sql_username"] forKey: @"sqlUsername"];
	[entity setValue: [srcDict objectForKey: @"realname"] forKey: @"fullName"];
	
	r = ((float)(UInt8)[[srcDict objectForKey: @"background_r"] unsignedIntValue]) / (float)0xFF;
	g = ((float)(UInt8)[[srcDict objectForKey: @"background_g"] unsignedIntValue]) / (float)0xFF;
	b = ((float)(UInt8)[[srcDict objectForKey: @"background_b"] unsignedIntValue]) / (float)0xFF;
	a = ((float)(UInt8)[[srcDict objectForKey: @"background_a"] unsignedIntValue]) / (float)0xFF;
	color = [NSColor colorWithCalibratedRed: r green: g blue: b alpha: a];
	colorData = [NSArchiver archivedDataWithRootObject: color];
	[entity setValue: colorData forKey: @"backgroundColorData"];
	
	r = ((float)(UInt8)[[srcDict objectForKey: @"text_r"] unsignedIntValue]) / (float)0xFF;
	g = ((float)(UInt8)[[srcDict objectForKey: @"text_g"] unsignedIntValue]) / (float)0xFF;
	b = ((float)(UInt8)[[srcDict objectForKey: @"text_b"] unsignedIntValue]) / (float)0xFF;
	a = ((float)(UInt8)[[srcDict objectForKey: @"text_a"] unsignedIntValue]) / (float)0xFF;
	color = [NSColor colorWithCalibratedRed: r green: g blue: b alpha: a];
	colorData = [NSArchiver archivedDataWithRootObject: color];
	[entity setValue: colorData forKey: @"textColorData"];
}


- (void)updateSQLUser: (id)entity fromDictionary: (NSDictionary *)srcDict
{
	id oldValue, newValue;
	float r_old, g_old, b_old, a_old;
	float r_new, g_new, b_new, a_new;
	NSColor *color;
	NSData *colorData;
	
	oldValue = [entity valueForKey: @"userID"];
	newValue = [srcDict valueForKey: @"user_id"];
	if (![oldValue isEqualToNumber: newValue])
		[entity setValue: newValue forKey: @"userID"];
	
	oldValue = [entity valueForKey: @"sqlUsername"];
	newValue = [srcDict valueForKey: @"sql_username"];
	if (![oldValue isEqualToString: newValue])
		[entity setValue: newValue forKey: @"sqlUsername"];
	
	oldValue = [entity valueForKey: @"fullName"];
	newValue = [srcDict valueForKey: @"realname"];
	if (![oldValue isEqualToString: newValue])
		[entity setValue: newValue forKey: @"fullName"];
	
	color = [entity valueForKey: @"backgroundColor"];
	[color getRed: &r_old green: &g_old blue: &b_old alpha: &a_old];
	r_new = ((float)(UInt8)[[srcDict objectForKey: @"background_r"] unsignedIntValue]) / (float)0xFF;
	g_new = ((float)(UInt8)[[srcDict objectForKey: @"background_g"] unsignedIntValue]) / (float)0xFF;
	b_new = ((float)(UInt8)[[srcDict objectForKey: @"background_b"] unsignedIntValue]) / (float)0xFF;
	a_new = ((float)(UInt8)[[srcDict objectForKey: @"background_a"] unsignedIntValue]) / (float)0xFF;
	if ((r_old != r_new) || (g_old != g_new) || (b_old != b_new) || (a_old != a_new))
	{
		color = [NSColor colorWithCalibratedRed: r_new green: g_new blue: b_new alpha: a_new];
		colorData = [NSArchiver archivedDataWithRootObject: color];
		[entity setValue: colorData forKey: @"backgroundColorData"];
	}
	
	color = [entity valueForKey: @"textColor"];
	[color getRed: &r_old green: &g_old blue: &b_old alpha: &a_old];
	r_new = ((float)(UInt8)[[srcDict objectForKey: @"text_r"] unsignedIntValue]) / (float)0xFF;
	g_new = ((float)(UInt8)[[srcDict objectForKey: @"text_g"] unsignedIntValue]) / (float)0xFF;
	b_new = ((float)(UInt8)[[srcDict objectForKey: @"text_b"] unsignedIntValue]) / (float)0xFF;
	a_new = ((float)(UInt8)[[srcDict objectForKey: @"text_a"] unsignedIntValue]) / (float)0xFF;
	if ((r_old != r_new) || (g_old != g_new) || (b_old != b_new) || (a_old != a_new))
	{
		color = [NSColor colorWithCalibratedRed: r_new green: g_new blue: b_new alpha: a_new];
		colorData = [NSArchiver archivedDataWithRootObject: color];
		[entity setValue: colorData forKey: @"textColorData"];
	}
}


- (void)syncSharedFolders
{
	NSString *sharedFoldersTable = NSLocalizedString(@"SharedFoldersTable", nil);
	NSString *query = [NSString stringWithFormat: @"SELECT * FROM %@", sharedFoldersTable];
	MCPResult *result = [_connection queryString: query];
	[self syncSharedFoldersWithRows: result];
}


- (void)syncSharedFoldersWithRows: (MCPResult *)rows
{
	NSError *error;
	NSManagedObjectContext *moc = [self managedObjectContext];
	NSFetchRequest *fetchRequest = [[[NSFetchRequest alloc] init] autorelease];
	NSEntityDescription *entity = [NSEntityDescription entityForName: @"SharedFolder" inManagedObjectContext: moc];
	[fetchRequest setEntity: entity];
	
	BOOL madeChanges = NO;
	NSDictionary *sharedFolder;
	while (sharedFolder = [rows fetchRowAsDictionary])
	{
		id sharedFolderEntity = nil;
		NSNumber *rowID = [sharedFolder objectForKey: @"id"];
		
		// is this folder already in our Core Data store?
		NSPredicate *predicate = [NSPredicate predicateWithFormat: @"id == %@", rowID];
		[fetchRequest setPredicate: predicate];
		NSArray *foundEntries = [moc executeFetchRequest: fetchRequest error: &error];
		if (!foundEntries && error)
			[NSException raise: NSGenericException format: @"%@", error];
		if (foundEntries)
		{
			unsigned int count = [foundEntries count];
			if ( 0 != count )
			{
				if (1 != [foundEntries count])
					[NSException raise: NSGenericException format: @"a duplicate ID (%@) was found", rowID];
				sharedFolderEntity = [foundEntries lastObject];
			}
		}
		
		// and populate the new entity
		if (sharedFolderEntity)
			[self updateSharedFolder: sharedFolderEntity fromDictionary: sharedFolder];
		else
			[self createSharedFolderFromDictionary: sharedFolder];
		madeChanges = YES;
	}
	
	if (madeChanges)
		[_foldersController rearrangeObjects];
}


- (void)createSharedFolderFromDictionary: (NSDictionary *)srcDict
{
	NSManagedObjectContext *moc = [self managedObjectContext];
	Folder *entity = [NSEntityDescription insertNewObjectForEntityForName: @"SharedFolder" inManagedObjectContext: moc];
    
	[entity setValue: [srcDict objectForKey: @"id"] forKey: @"id"];
	[entity setValue: [srcDict objectForKey: @"name"] forKey: @"name"];
    [entity setFirstSortKeyForBase: 500];
}


- (void)updateSharedFolder: (id)entity fromDictionary: (NSDictionary *)srcDict
{
	id oldValue, newValue;

	oldValue = [entity valueForKey: @"name"];
	newValue = [srcDict valueForKey: @"name"];
	if (![oldValue isEqualToString: newValue])
		[entity setValue: newValue forKey: @"name"];
}


- (void)syncContestEntries
{
	// create the base query
	NSUserDefaults *ud = [NSUserDefaults standardUserDefaults];
	NSString *query = [NSString stringWithFormat: @"SELECT * FROM %@", NSLocalizedString(@"SubmissionsTable", nil)];
	
	// if we've already read data out of the database, only get what's been modified since then
	NSDate *lastDBRead = [ud objectForKey: @"LastDatabaseRead"];
	if (lastDBRead)
	{
		unsigned int timeInterval = [self _databaseTimeIntervalForDate: lastDBRead];
		query = [query stringByAppendingString: [NSString stringWithFormat: @" WHERE (UNIX_TIMESTAMP(modified_on)-%i) > 0", timeInterval]];
	}
	
	// process it
	MCPResult *result = [_connection queryString: query];
	[self syncContestEntriesWithRows: result];
}


- (void)syncContestEntriesWithRows: (MCPResult *)rows
{
	NSError *error;
	NSManagedObjectContext *moc = [self managedObjectContext];
	NSFetchRequest *fetchRequest = [[[NSFetchRequest alloc] init] autorelease];
	NSEntityDescription *entity = [NSEntityDescription entityForName: @"ContestEntry" inManagedObjectContext: moc];
	[fetchRequest setEntity: entity];
	
	NSDictionary *contestEntry;
	while (contestEntry = [rows fetchRowAsDictionary])
	{
		id contestEntity = nil;
		NSNumber *rowID = [contestEntry objectForKey: @"id"];
		
		// is this contest entry already in our Core Data store?
		NSPredicate *predicate = [NSPredicate predicateWithFormat: @"id == %@", rowID];
		[fetchRequest setPredicate: predicate];
		NSArray *foundEntries = [moc executeFetchRequest: fetchRequest error: &error];
		if (!foundEntries && error)
			[NSException raise: NSGenericException format: @"%@", error];
		if (foundEntries)
		{
			unsigned int count = [foundEntries count];
			if ( 0 != count )
			{
				if (1 != [foundEntries count])
					[NSException raise: NSGenericException format: @"a duplicate ID (%@) was found", rowID];
				contestEntity = [foundEntries lastObject];
			}
		}
		
		// if not, create a new entity to hold it
		if (!contestEntity)
			contestEntity = [NSEntityDescription insertNewObjectForEntityForName: @"ContestEntry" inManagedObjectContext: moc];
		
		// and populate the new entity
		[self populateContestEntry: contestEntity fromDictionary: contestEntry];		
	}
}


- (void)populateContestEntry: (id)entity fromDictionary: (NSDictionary *)srcDict
{
	NSCalendarDate *time = [NSCalendarDate dateWithTimeIntervalSince1970: [[srcDict objectForKey: @"time"] unsignedIntValue]];
	[time setTimeZone: [self _databaseTimezone]];
	
	[entity setValue: [srcDict objectForKey: @"id"] forKey: @"id"];
	[entity setValue: [srcDict objectForKey: @"app_name"] forKey: @"appName"];
	[entity setValue: [srcDict objectForKey: @"idea"] forKey: @"idea"];
	[entity setValue: [srcDict objectForKey: @"full_name"] forKey: @"fullName"];
	[entity setValue: [srcDict objectForKey: @"phone"] forKey: @"phone"];
	[entity setValue: [srcDict objectForKey: @"email"] forKey: @"email"];
	[entity setValue: [srcDict objectForKey: @"about"] forKey: @"about"];
	[entity setValue: [srcDict objectForKey: @"agree"] forKey: @"agree"];
	[entity setValue: time forKey: @"time"];
	[entity setValue: [srcDict objectForKey: @"ip"] forKey: @"ipAddress"];
	[entity setValue: [srcDict objectForKey: @"username"] forKey: @"username"];
	[entity setValue: [srcDict objectForKey: @"modified_on"] forKey: @"modifiedDate"];
}


- (void)syncEntryComments
{
	// create the base query
	NSUserDefaults *ud = [NSUserDefaults standardUserDefaults];
	NSString *query = [NSString stringWithFormat: @"SELECT * FROM %@", NSLocalizedString(@"CommentsTable", nil)];
	
	// if we've already read data out of the database, only get what's been uploaded since then
	NSDate *lastDBRead = [ud objectForKey: @"LastDatabaseRead"];
	if (lastDBRead)
	{
		unsigned int timeInterval = [self _databaseTimeIntervalForDate: lastDBRead];
		query = [query stringByAppendingString: [NSString stringWithFormat: @" WHERE (UNIX_TIMESTAMP(uploaded_on)-%i) > 0", timeInterval]];
	}
	
	// process it
	MCPResult *result = [_connection queryString: query];
	[self syncEntryCommentsWithRows: result];
}


- (void)syncEntryCommentsWithRows: (MCPResult *)rows
{
	NSError *error;
	NSManagedObjectContext *moc = [self managedObjectContext];
	NSFetchRequest *fetchRequest = [[[NSFetchRequest alloc] init] autorelease];
	NSEntityDescription *entity = [NSEntityDescription entityForName: @"EntryComment" inManagedObjectContext: moc];
	[fetchRequest setEntity: entity];
	
	NSDictionary *entryComment;
	while (entryComment = [rows fetchRowAsDictionary])
	{
		id commentEntity = nil;
		NSNumber *rowID = [entryComment objectForKey: @"id"];
		
		// is this comment already in our Core Data store?
		NSPredicate *predicate = [NSPredicate predicateWithFormat: @"id == %@", rowID];
		[fetchRequest setPredicate: predicate];
		NSArray *foundEntries = [moc executeFetchRequest: fetchRequest error: &error];
		if (!foundEntries && error)
			[NSException raise: NSGenericException format: @"%@", error];
		if (foundEntries)
		{
			unsigned int count = [foundEntries count];
			if ( 0 != count )
			{
				if (1 != [foundEntries count])
					[NSException raise: NSGenericException format: @"a duplicate ID (%@) was found", rowID];
				commentEntity = [foundEntries lastObject];
			}
		}
		
		// if not, create a new entity to hold it
		if (!commentEntity)
			commentEntity = [NSEntityDescription insertNewObjectForEntityForName: @"EntryComment" inManagedObjectContext: moc];
		
		[self populateEntryComments: commentEntity fromDictionary: entryComment];		
	}
}


- (void)populateEntryComments: (id)entity fromDictionary: (NSDictionary *)srcDict
{
	NSCalendarDate *createdDate = [self _dateFromDatabase: [srcDict objectForKey: @"created_on"]];
	
	// find the contest entry associated with this comment
	NSNumber *submissionID = [srcDict objectForKey: @"submission_id"];
	MyManagedObjectContext *moc = [self managedObjectContext];
	NSManagedObject *contestEntry = [moc contestEntryWithID: submissionID];
	if ( ! contestEntry )
	{
		[moc deleteObject: entity];
		return;
	}
	
	// find the sqlUser
	NSManagedObject *sqlUser = [moc sqlUserForSQLUserID: [srcDict objectForKey: @"sql_user_id"]];
	
	// and set it all
	[entity setValue: [srcDict objectForKey: @"id"] forKey: @"id"];
	[entity setValue: createdDate forKey: @"createdDate"];
	[entity setValue: [srcDict objectForKey: @"rtfdComment"] forKey: @"rtfdComment"];
	[entity setValue: sqlUser forKey: @"author"];
	[entity setValue: contestEntry forKey: @"entry"];
}


- (void)syncRatings
{
	// create the base query
	NSUserDefaults *ud = [NSUserDefaults standardUserDefaults];
	NSString *query = [NSString stringWithFormat: @"SELECT * FROM %@", NSLocalizedString(@"RatingsTable", nil)];
	
	// if we've already read data out of the database, only get what's been uploaded since then
	NSDate *lastDBRead = [ud objectForKey: @"LastDatabaseRead"];
	if (lastDBRead)
	{
		unsigned int timeInterval = [self _databaseTimeIntervalForDate: lastDBRead];
		query = [query stringByAppendingString: [NSString stringWithFormat: @" WHERE (UNIX_TIMESTAMP(uploaded_on)-%i) > 0", timeInterval]];
	}
	
	// process it
	MCPResult *result = [_connection queryString: query];
	[self syncRatingsWithRows: result];
}


- (void)syncRatingsWithRows: (MCPResult *)rows
{
	NSError *error;
	MyManagedObjectContext *moc = [self managedObjectContext];
	NSFetchRequest *fetchRequest = [[[NSFetchRequest alloc] init] autorelease];
	NSEntityDescription *entity = [NSEntityDescription entityForName: @"Rating" inManagedObjectContext: moc];
	[fetchRequest setEntity: entity];
	
	NSDictionary *rating;
	while (rating = [rows fetchRowAsDictionary])
	{
		id ratingEntity = nil;
		NSNumber *rowID = [rating objectForKey: @"id"];
		
		// is this rating already in our Core Data store?
		NSPredicate *predicate = [NSPredicate predicateWithFormat: @"id == %@", rowID];
		[fetchRequest setPredicate: predicate];
		NSArray *foundEntries = [moc executeFetchRequest: fetchRequest error: &error];
		if (!foundEntries && error)
			[NSException raise: NSGenericException format: @"%@", error];
		if (foundEntries)
		{
			unsigned int count = [foundEntries count];
			if ( 0 != count )
			{
				if (1 != [foundEntries count])
					[NSException raise: NSGenericException format: @"a duplicate ID (%@) was found", rowID];
				ratingEntity = [foundEntries lastObject];
			}
		}
		
		// find the contest entry associated with this rating
		NSNumber *submissionID = [rating objectForKey: @"submission_id"];
		NSManagedObject *contestEntry = [moc contestEntryWithID: submissionID];
		if ( ! contestEntry )
		{
			NSLog(@"found a rating that does not refer to an existing contest entry, ID = %@", submissionID);
			continue;
		}
		
		// find the sqlUser
		NSManagedObject *sqlUser = [moc sqlUserForSQLUserID: [rating objectForKey: @"sql_user_id"]];
		
		// and populate the new entity
		if (ratingEntity)
			[self updateRating: ratingEntity fromDictionary: rating sqlUser: sqlUser contestEntry: contestEntry];
		else
			[self createRatingFromDictionary: rating sqlUser: sqlUser contestEntry: contestEntry];
	}
}


- (void)createRatingFromDictionary: (NSDictionary *)srcDict sqlUser: (NSManagedObject *)sqlUser contestEntry: (NSManagedObject *)contestEntry
{
	NSCalendarDate *createdDate = [self _dateFromDatabase: [srcDict objectForKey: @"created_on"]];

	NSManagedObjectContext *moc = [self managedObjectContext];
	id entity = [NSEntityDescription insertNewObjectForEntityForName: @"Rating" inManagedObjectContext: moc];
	
	[entity setValue: [srcDict objectForKey: @"id"] forKey: @"id"];
	[entity setValue: createdDate forKey: @"createdDate"];
	[entity setValue: sqlUser forKey: @"sqlUser"];
	[entity setValue: contestEntry forKey: @"entry"];
	[entity setValue: [srcDict objectForKey: @"rating"] forKey: @"rating"];
}


- (void)updateRating: (id)entity fromDictionary: (NSDictionary *)srcDict sqlUser: (NSManagedObject *)sqlUser contestEntry: (NSManagedObject *)contestEntry
{
	id oldValue, newValue;
	NSString *oldUUID, *newUUID;
	
	oldUUID = [entity valueForKeyPath: @"sqlUser.uuid"];
	newUUID = [sqlUser valueForKey: @"uuid"];
	if (![oldUUID isEqualToString: newUUID])
		[entity setValue: contestEntry forKey: @"sqlUser"];
	
	oldUUID = [entity valueForKeyPath: @"entry.uuid"];
	newUUID = [contestEntry valueForKey: @"uuid"];
	if (![oldUUID isEqualToString: newUUID])
		[entity setValue: contestEntry forKey: @"entry"];
	
	oldValue = [entity valueForKey: @"rating"];
	newValue = [srcDict valueForKey: @"rating"];
	if (![oldValue isEqualToNumber: newValue])
	{
		[entity setValue: newValue forKey: @"rating"];
		[[self managedObjectContext] categorizeContestEntry: [entity valueForKey: @"entry"]];
	}
}


- (void)syncSharedFolderSubmissions
{
	// create the base query
	NSString *query = [NSString stringWithFormat: @"SELECT * FROM %@", NSLocalizedString(@"SharedFoldersSubmissionsTable", nil)];
	
	// process it
	MCPResult *result = [_connection queryString: query];
	[self syncSharedFolderSubmissionsWithRows: result];
}


- (void)syncSharedFolderSubmissionsWithRows: (MCPResult *)rows
{
	NSError *error;
	NSManagedObjectContext *moc = [self managedObjectContext];
	
	NSFetchRequest *submissionFetchRequest = [[[NSFetchRequest alloc] init] autorelease];
	NSEntityDescription *submissionEntity = [NSEntityDescription entityForName: @"ContestEntry" inManagedObjectContext: moc];
	[submissionFetchRequest setEntity: submissionEntity];
	
	NSFetchRequest *folderFetchRequest = [[[NSFetchRequest alloc] init] autorelease];
	NSEntityDescription *folderEntity = [NSEntityDescription entityForName: @"SharedFolder" inManagedObjectContext: moc];
	[folderFetchRequest setEntity: folderEntity];
	
	NSDictionary *entry;
	while (entry = [rows fetchRowAsDictionary])
	{
		NSPredicate *predicate;
		NSArray *foundEntries;
		
		NSNumber *submissionID = [entry objectForKey: @"submission_id"];
		predicate = [NSPredicate predicateWithFormat: @"id == %@", submissionID];
		[submissionFetchRequest setPredicate: predicate];
		foundEntries = [moc executeFetchRequest: submissionFetchRequest error: &error];
		if (!foundEntries && error)
			[NSException raise: NSGenericException format: @"%@", error];
		if (1 != [foundEntries count])
		{
			NSLog(@"found a reference to a contest entry that isn't present.  The id is %@", submissionID);
			continue;
		}
		NSManagedObject *contestEntry = [foundEntries lastObject];
		
		NSNumber *folderID = [entry objectForKey: @"shared_folder_id"];
		predicate = [NSPredicate predicateWithFormat: @"id == %@", folderID];
		[folderFetchRequest setPredicate: predicate];
		foundEntries = [moc executeFetchRequest: folderFetchRequest error: &error];
		if (!foundEntries && error)
			[NSException raise: NSGenericException format: @"%@", error];
		if (1 != [foundEntries count])
		{
			NSLog(@"found a reference to a shared folder that isn't present.  The id is %@", folderID);
			continue;
		}
		NSManagedObject *sharedFolder = [foundEntries lastObject];
		
		[[sharedFolder mutableSetValueForKey: @"entries"] addObject: contestEntry];
	}
}


#pragma mark Uploading


- (void)uploadDirtyObjects
{
	NSParameterAssert( _connection != nil );
	MyManagedObjectContext *moc = [self managedObjectContext];
	
	NSArray *dirtyObjects = [moc dirtyObjects];
	unsigned i, count = [dirtyObjects count];
	for (i = 0; i < count; ++i)
	{
		id dirtyObjectWrapper = [dirtyObjects objectAtIndex: i];
		NSString *uuid = [dirtyObjectWrapper valueForKey: @"uuid"];
		NSError *error;
		NSFetchRequest *fetchRequest = [[[NSFetchRequest alloc] init] autorelease];
		NSEntityDescription *entity = [NSEntityDescription entityForName: [dirtyObjectWrapper valueForKey: @"nameOfClass"] inManagedObjectContext: moc];
		[fetchRequest setEntity: entity];
		NSPredicate *predicate = [NSPredicate predicateWithFormat: @"uuid == %@", uuid];
		[fetchRequest setPredicate: predicate];
		NSArray *foundObjects = [moc executeFetchRequest: fetchRequest error: &error];
		if ( !foundObjects && error )
			[NSException raise: NSGenericException format: @"%@", error];
		NSParameterAssert( 1 == [foundObjects count] );
		NSManagedObject *dirtyObject = [foundObjects lastObject];
		NSParameterAssert( dirtyObject != nil );
		
		NSString *entityName = [[dirtyObject entity] managedObjectClassName];
		if ([entityName isEqualToString: @"Rating"])
			[self uploadRating: dirtyObject];
		else if ([entityName isEqualToString: @"EntryComment"])
			[self uploadEntryComment: dirtyObject];
		else if ([entityName isEqualToString: @"SharedFolder"])
			[self uploadSharedFolder: dirtyObject];
		else
			[NSException raise: NSGenericException format: @"unrecognized dirty object of class \"%@\"", entityName];
		
		[moc deleteObject: dirtyObjectWrapper];
	}
}


- (void)uploadRating: (NSManagedObject *)ratingContainer
{
	MyManagedObjectContext *moc =	[self managedObjectContext];
	NSString *ratingsTable =		NSLocalizedString(@"RatingsTable", nil);
	NSString *submissionID =		[_connection quoteObject: [ratingContainer valueForKeyPath: @"entry.id"]];
	NSString *createdDate =			[self _dateForDatabase: [ratingContainer valueForKey: @"createdDate"]];
	NSString *rating =				[_connection quoteObject: [ratingContainer valueForKey: @"rating"]];
	
	// lets just be safe and verify that we're not updating someone else's comment
	NSNumber *ratingUserID = [ratingContainer valueForKeyPath: @"sqlUser.id"];
	NSString *ratingUsername = [[moc sqlUserForSQLUserID: ratingUserID] valueForKey: @"sqlUsername"];
	NSString *mySQLUsername = [[[NSUserDefaultsController sharedUserDefaultsController] values] valueForKey: @"LastValidSQLUsername"];
	NSParameterAssert( [mySQLUsername isEqualToString: ratingUsername] );
	
	NSString *sqlUserID = [_connection quoteObject: ratingUserID];
	NSString *query = [NSString stringWithFormat: @"SELECT id FROM %@ WHERE submission_id=%@ AND sql_user_id=%@", ratingsTable, submissionID, sqlUserID];
	MCPResult *result = [_connection queryString: query];
	NSParameterAssert( result != nil );
	unsigned long numResults = [result numOfRows];
	
	// if it doesn't already exist, add it
	if ( 0 == numResults )
	{
		query = [NSString stringWithFormat: @"INSERT INTO %@ SET submission_id=%@, sql_user_id=%@, rating=%@, created_on=%@", ratingsTable, submissionID, sqlUserID, rating, createdDate];
		unsigned long long newID = [_connection insertQuery: query];
		NSParameterAssert( [_connection getLastErrorID] == 0 );
		[ratingContainer setValue: [NSNumber numberWithUnsignedInt: newID] forKey: @"id"];
	}
	
	// otherwise, update the existing record
	else if ( 1 == numResults )
	{
		NSString *rowID = [_connection quoteObject: [[result fetchRowAsDictionary] objectForKey: @"id"]];
		query = [NSString stringWithFormat: @"UPDATE %@ SET rating=%@ WHERE id=%@", ratingsTable, rating, rowID];
		[_connection queryString: query];
		NSParameterAssert( [_connection getLastErrorID] == 0 );
	}
	else
		[NSException raise: NSGenericException format: @"multiple database entries exist for the same rating"];
}


- (void)uploadEntryComment: (NSManagedObject *)comment
{
	MyManagedObjectContext *moc =	[self managedObjectContext];
	NSString *commentsTable =		NSLocalizedString(@"CommentsTable", nil);
	NSString *submissionID =		[_connection quoteObject: [comment valueForKeyPath: @"entry.id"]];
	NSString *commentString =		[_connection quoteObject: [comment valueForKey: @"comment"]];
	NSString *rtfdComment =			[_connection quoteObject: [comment valueForKey: @"rtfdComment"]];
	NSString *createdDate =			[self _dateForDatabase: [comment valueForKey: @"createdDate"]];
	NSString *sqlUserID =			[_connection quoteObject: [[moc mySQLUser] valueForKey: @"id"]];
	
	NSString *query = [NSString stringWithFormat: @"INSERT INTO %@ SET submission_id=%@, sql_user_id=%@, created_on=%@, comment=%@, rtfdComment=%@", commentsTable, submissionID, sqlUserID, createdDate, commentString, rtfdComment];
	unsigned long long newID = [_connection insertQuery: query];
	NSParameterAssert( [_connection getLastErrorID] == 0 );
	[comment setValue: [NSNumber numberWithUnsignedInt: newID] forKey: @"id"];
}


- (void)uploadSharedFolder: (NSManagedObject *)sharedFolderContainer
{
	if ([[sharedFolderContainer valueForKey: @"needsSync"] boolValue])
		[self uploadSharedFolderStructure: sharedFolderContainer];
	[self uploadSharedFolderContents: sharedFolderContainer];
}


- (void)uploadSharedFolderStructure: (NSManagedObject *)sharedFolderContainer
{
	NSString *sharedFoldersTable =	NSLocalizedString(@"SharedFoldersTable", nil);
    NSNumber *rowID =               [sharedFolderContainer valueForKey: @"id"];
	NSString *name =                [_connection quoteObject: [sharedFolderContainer valueForKey: @"name"]];
	
	// if it doesn't already exist, add it
	if ( -1 == [rowID intValue] )
	{
		NSString *query = [NSString stringWithFormat: @"INSERT INTO %@ SET name=%@", sharedFoldersTable, name];
		unsigned long long newID = [_connection insertQuery: query];
		NSParameterAssert( [_connection getLastErrorID] == 0 );
		[sharedFolderContainer setValue: [NSNumber numberWithUnsignedInt: newID] forKey: @"id"];
	}
	
	// otherwise, update the existing record
	else 
	{
        NSString *idString = [_connection quoteObject: rowID];
		NSString *query = [NSString stringWithFormat: @"UPDATE %@ SET name=%@ WHERE id=%@", sharedFoldersTable, name, idString];
		[_connection queryString: query];
		NSParameterAssert( [_connection getLastErrorID] == 0 );
	}

	[sharedFolderContainer setValue: [NSNumber numberWithBool: NO] forKey: @"needsSync"];
}


- (void)uploadSharedFolderContents: (NSManagedObject *)sharedFolderContainer
{
	NSString *table =	NSLocalizedString(@"SharedFoldersSubmissionsTable", nil);
	NSString *folderID =        [_connection quoteObject: [sharedFolderContainer valueForKeyPath: @"id"]];
	NSEnumerator *submissionEnumerator = [[sharedFolderContainer valueForKey: @"entries"] objectEnumerator];
	NSManagedObject *submission;
	
	while (submission = [submissionEnumerator nextObject])
	{
		NSString *submissionID =        [_connection quoteObject: [submission valueForKeyPath: @"id"]];
		
		NSString *query = [NSString stringWithFormat: @"SELECT * FROM %@ WHERE shared_folder_id=%@ AND submission_id=%@", table, folderID, submissionID];
		MCPResult *result = [_connection queryString: query];
		NSParameterAssert( result != nil );
		unsigned long numResults = [result numOfRows];
		
		// if it doesn't already exist, add it
		if ( 0 == numResults )
		{
			query = [NSString stringWithFormat: @"INSERT INTO %@ SET shared_folder_id=%@, submission_id=%@", table, folderID, submissionID];
			[_connection queryString: query];
			NSParameterAssert( [_connection getLastErrorID] == 0 );
		}
		
		// otherwise, if it's already there, do nothing
		else if ( 1 == numResults )
		{
		}
		else
			[NSException raise: NSGenericException format: @"multiple database entries exist for the same rating"];
	}
}


#pragma mark Comments


- (IBAction)addComment: (id)sender
{
	[self setValue: [NSData data] forKey: @"newComment"];
	[NSApp beginSheet: _commentSheet modalForWindow: [self window] modalDelegate: self didEndSelector: @selector(commentSheetDidEnd:returnCode:contextInfo:) contextInfo: nil];
}


- (IBAction)commitComment: (NSButton *)sender
{
	[NSApp endSheet: _commentSheet returnCode: NSOKButton];
}


- (IBAction)cancelComment: (NSButton *)sender
{
	[NSApp endSheet: _commentSheet returnCode: NSCancelButton];
}


- (void)commentSheetDidEnd: (NSWindow *)sheet returnCode: (int)returnCode contextInfo: (void *)contextInfo
{
	[sheet orderOut: nil];
	switch (returnCode)
	{
		case NSOKButton:
			[self createComment];
			break;
		case NSCancelButton:
			break;
	}
}


- (void)createComment
{
	[_commentController commitEditing];
	MyManagedObjectContext *moc = [self managedObjectContext];
	
	NSString *username = [[[NSUserDefaultsController sharedUserDefaultsController] values] valueForKey: @"LastValidSQLUsername"];
	NSManagedObject *sqlUser = [moc sqlUserForSQLUsername: username];
	
	NSManagedObject *comment = [NSEntityDescription insertNewObjectForEntityForName: @"EntryComment" inManagedObjectContext: moc];
	[comment setValue: [NSNumber numberWithInt: -1] forKey: @"id"];
	[comment setValue: [self valueForKey: @"newComment"] forKey: @"rtfdComment"];
	[comment setValue: sqlUser forKey: @"author"];
	[comment setValue: [[_submissionsController selectedObjects] lastObject] forKey: @"entry"];
	
	[moc markObjectAsDirty: comment];
}


#pragma mark Folders


- (IBAction)newFolder: (id)sender
{
	[self setValue: @"" forKey: @"newFolderName"];
	[_newFolderPrompt setStringValue: NSLocalizedString(@"NewFolderPrompt", nil)];
	[NSApp beginSheet: _newFolderSheet modalForWindow: [self window] modalDelegate: self didEndSelector: @selector(newFolderSheetDidEnd:returnCode:contextInfo:) contextInfo: nil];
}


- (IBAction)newSharedFolder: (id)sender
{
	[self setValue: @"" forKey: @"newFolderName"];
	[_newFolderPrompt setStringValue: NSLocalizedString(@"NewSharedFolderPrompt", nil)];
	[NSApp beginSheet: _newFolderSheet modalForWindow: [self window] modalDelegate: self didEndSelector: @selector(newSharedFolderSheetDidEnd:returnCode:contextInfo:) contextInfo: nil];
}


- (void)_selectNewFolder: (NSManagedObject *)folder
{
	[_foldersController rearrangeObjects];
	unsigned int index = [[_foldersController arrangedObjects] indexOfObject: folder];
	[_foldersController setSelectionIndex: index];
}


- (void)_createNewFolder
{
	NSManagedObjectContext *moc = [self managedObjectContext];
	NSManagedObject *folder = [NSEntityDescription insertNewObjectForEntityForName: @"Folder" inManagedObjectContext: moc];
    [folder setValue: [NSNumber numberWithInt: 1000] forKey: @"sortKey"];
    [folder setValue: [[_newFolderName copy] autorelease] forKey: @"name"];
	[self _selectNewFolder: folder];
}


- (void)_createNewSharedFolder
{
	MyManagedObjectContext *moc = [self managedObjectContext];
	NSManagedObject *folder = [NSEntityDescription insertNewObjectForEntityForName: @"SharedFolder" inManagedObjectContext: moc];
    [folder setValue: [NSNumber numberWithBool: NO] forKey: @"canBeDeleted"];
    [folder setValue: [NSNumber numberWithInt: -1] forKey: @"id"];
    [folder setValue: [[_newFolderName copy] autorelease] forKey: @"name"];
    [folder setValue: [NSNumber numberWithInt: 500] forKey: @"sortKey"];
	[folder setValue: [NSNumber numberWithBool: YES] forKey: @"needsSync"];
	[self _selectNewFolder: folder];
}


- (void)newFolderSheetDidEnd: (NSWindow *)sheet returnCode: (int)returnCode contextInfo: (void *)contextInfo
{
	[sheet orderOut: nil];
	switch (returnCode)
	{
		case NSOKButton:
			[self _createNewFolder];
			break;
		case NSCancelButton:
			break;
	}
}


- (void)newSharedFolderSheetDidEnd: (NSWindow *)sheet returnCode: (int)returnCode contextInfo: (void *)contextInfo
{
	[sheet orderOut: nil];
	switch (returnCode)
	{
		case NSOKButton:
			[self _createNewSharedFolder];
			break;
		case NSCancelButton:
			break;
	}
}


- (IBAction)createFolderPressed: (NSButton *)sender
{
	[NSApp endSheet: _newFolderSheet returnCode: NSOKButton];
}


- (IBAction)cancelCreateFolderPressed: (NSButton *)sender
{
	[NSApp endSheet: _newFolderSheet returnCode: NSCancelButton];
}


- (BOOL)isFolderNameValid
{
	return _newFolderName && [_newFolderName length] > 0;
}


#pragma mark Status


- (IBAction)markAsRead: (id)sender
{
	NSArray *selectedObjects = [_submissionsController selectedObjects];
	[selectedObjects setValue: [NSNumber numberWithBool: YES] forKey: @"read"];
}


- (IBAction)markAsUnread: (id)sender
{
	NSArray *selectedObjects = [_submissionsController selectedObjects];
	[selectedObjects setValue: [NSNumber numberWithBool: NO] forKey: @"read"];
}


#pragma mark Misc


- (void)setStatus: (NSString *)status kind: (SlapperStatus)statusKind
{
	[self setValue: status forKey: @"status"];
	switch (statusKind)
	{
		case kSlapperErrorStatus:
			[self setValue: [NSColor redColor] forKey: @"statusColor"];
			break;
		default:
			[self setValue: [NSColor disabledControlTextColor] forKey: @"statusColor"];
	}
}


- (void)createSortDescriptorsFromPrefs
{
	NSString *key;
	BOOL ascending;
	NSSortDescriptor *sortDescriptor;
	NSUserDefaults *ud = [NSUserDefaults standardUserDefaults];
	
	key = [ud objectForKey: @"ContestEntrySortKey"];
	ascending = [ud boolForKey: @"ContestEntrySortAscending"];
	if ( ! key )
	{
		key = @"appName";
		ascending = YES;
	}
	sortDescriptor = [[[NSSortDescriptor alloc] initWithKey: key ascending: ascending] autorelease];
	_sortDescriptors = [[NSArray alloc] initWithObjects: sortDescriptor, nil];
	
	key = [ud objectForKey: @"CommentsSortKey"];
	ascending = [ud boolForKey: @"CommentsSortAscending"];
	if ( ! key )
	{
		key = @"createdDate";
		ascending = YES;
	}
	sortDescriptor = [[[NSSortDescriptor alloc] initWithKey: key ascending: ascending] autorelease];
	_commentsSortDescriptors = [[NSArray alloc] initWithObjects: sortDescriptor, nil];
}


- (void)saveSortDescriptorsToPrefs
{
	NSSortDescriptor *sortDescriptor;
	NSUserDefaults *ud = [NSUserDefaults standardUserDefaults];
	
	sortDescriptor = [_sortDescriptors objectAtIndex: 0];
	[ud setValue: [sortDescriptor key] forKey: @"ContestEntrySortKey"];
	[ud setValue: [NSNumber numberWithBool: [sortDescriptor ascending]] forKey: @"ContestEntrySortAscending"];
	
	sortDescriptor = [_commentsSortDescriptors objectAtIndex: 0];
	[ud setValue: [sortDescriptor key] forKey: @"CommentsSortKey"];
	[ud setValue: [NSNumber numberWithBool: [sortDescriptor ascending]] forKey: @"CommentsSortAscending"];
}


- (BOOL)handleKeyboardEquivalent: (NSEvent *)theEvent
{
	if (0 == ([theEvent modifierFlags] & NSDeviceIndependentModifierFlagsMask))
	{
		NSArray *selectedObjects = [_submissionsController selectedObjects];
		unsigned i, count = [selectedObjects count];
		if (count)
		{
			int rating = -1;
			
			NSString *characters = [theEvent characters];
			if ( [characters isEqualToString: @"`"] )
				rating = 0;
			else if ( [characters isEqualToString: @"1"] )
				rating = 1;
			else if ( [characters isEqualToString: @"2"] )
				rating = 2;
			else if ( [characters isEqualToString: @"3"] )
				rating = 3;
			else if ( [characters isEqualToString: @"4"] )
				rating = 4;
			else if ( [characters isEqualToString: @"5"] )
				rating = 5;
			
			if ( -1 != rating )
			{
				for (i = 0; i < count; ++i)
				{
					NSManagedObject *contestEntry = [selectedObjects objectAtIndex: i];
					[contestEntry setValue: [NSNumber numberWithInt: rating] forKey: @"rating"];
				}
				return YES;
			}
			
			int folderID = -1;
			if ( [characters isEqualToString: @"q"] )
				folderID = 2;
			else if ( [characters isEqualToString: @"w"] )
				folderID = 1;
			
			if ( -1 != folderID )
			{
				NSError *error;
				MyManagedObjectContext *moc = [self managedObjectContext];
				NSFetchRequest *fetchRequest = [[[NSFetchRequest alloc] init] autorelease];
				NSEntityDescription *entity = [NSEntityDescription entityForName: @"SharedFolder" inManagedObjectContext: moc];
				[fetchRequest setEntity: entity];
				NSPredicate *predicate = [NSPredicate predicateWithFormat: @"id == %d", folderID];
				[fetchRequest setPredicate: predicate];
				NSArray *foundFolders = [moc executeFetchRequest: fetchRequest error: &error];
				if (!foundFolders && error)
					[NSException raise: NSGenericException format: @"%@", error];
				if (1 != [foundFolders count])
				{
					NSLog(@"could not find shared folder with ID %d", folderID);
					return NO;
				}
				NSManagedObject *folder = [foundFolders lastObject];
				[[folder mutableSetValueForKey: @"entries"] addObjectsFromArray: selectedObjects];
				[moc markObjectAsDirty: folder];
				[_foldersController rearrangeObjects];
				return YES;
			}
		}
	}
	return NO;
}


- (NSUndoManager *)windowWillReturnUndoManager: (NSWindow *)sender
{
    return [[self managedObjectContext] undoManager];
}


- (void)observeValueForKeyPath:(NSString *)keyPath ofObject:(id)object change:(NSDictionary *)change context:(void *)context
{
	if (context && [(id)context isEqualToString: @"SubmissionsSelection"])
	{
		NSArray *selectedObjects = [_submissionsController selectedObjects];
		if ( 1 == [selectedObjects count] )
			[selectedObjects setValue: [NSNumber numberWithBool: YES] forKey: @"read"];
	}
}


#pragma mark TableView DataSource


- (int)numberOfRowsInTableView:(NSTableView *)aTableView
{  return 0;  }


- (id)tableView:(NSTableView *)aTableView objectValueForTableColumn:(NSTableColumn *)aTableColumn row:(int)rowIndex
{  return nil;  }


- (BOOL)tableView:(NSTableView *)tv writeRowsWithIndexes:(NSIndexSet *)rowIndexes toPasteboard:(NSPasteboard*)pboard
{
	if (tv == _submissionsTableView)
	{
		[pboard declareTypes: [NSArray arrayWithObject: kSubmissionPasteboardType] owner: self];
		NSArray *selectedObjects = [[_submissionsController arrangedObjects] objectsAtIndexes: rowIndexes];
		NSMutableArray *urls = [NSMutableArray array];
		unsigned i, count = [selectedObjects count];
		for (i = 0; i < count; ++i)
		{
			[urls addObject: [[[[selectedObjects objectAtIndex: i] objectID] URIRepresentation] absoluteString]];
		}
		NSParameterAssert( [urls count] > 0 );
		[pboard setPropertyList: urls forType: kSubmissionPasteboardType];
		return YES;
	}
	
	return NO;
}


- (NSDragOperation)tableView:(NSTableView*)tv validateDrop:(id <NSDraggingInfo>)info proposedRow:(int)row proposedDropOperation:(NSTableViewDropOperation)op
{
	if (tv == _foldersTableView)
	{
		NSArray *arrangedObjects = [_foldersController arrangedObjects];
		if (row < [arrangedObjects count])
		{
			NSManagedObject *folder = [arrangedObjects objectAtIndex: row];
			NSString *predicateString = [folder valueForKey: @"predicateString"];
			if ( nil == predicateString )
			{
				NSIndexSet *selectionIndexes = [_foldersController selectionIndexes];
				if (![selectionIndexes containsIndex: row])
				{
					if (NSTableViewDropAbove == op)
						[tv setDropRow: row dropOperation: NSTableViewDropOn];
					return NSDragOperationCopy;
				}
			}
		}
	}
	return NSDragOperationNone;
}


- (BOOL)tableView:(NSTableView *)tv acceptDrop:(id <NSDraggingInfo>)info row:(int)row dropOperation:(NSTableViewDropOperation)operation
{
	if (tv == _foldersTableView)
	{
		// grab the destination folder
		NSArray *arrangedObjects = [_foldersController arrangedObjects];
		NSManagedObject *folder = [arrangedObjects objectAtIndex: row];

		// figure out our predicate to fetch the objects we need
		NSPasteboard* pboard = [info draggingPasteboard];
		MyManagedObjectContext *moc = [self managedObjectContext];
		NSPersistentStoreCoordinator *psc = [self persistentStoreCoordinator];
		NSArray *urls = [pboard propertyListForType: kSubmissionPasteboardType];
		NSMutableArray *oids = [NSMutableArray array];
		unsigned i, count = [urls count];
		for (i = 0; i < count; ++i)
		{
			[oids addObject: [psc managedObjectIDForURIRepresentation: [NSURL URLWithString: [urls objectAtIndex: i]]]];
		}
		NSPredicate *predicate = [NSPredicate predicateWithFormat: @"SELF in %@", oids];
		
		// fetch 'em
		NSError *error;
		NSFetchRequest *fetchRequest = [[[NSFetchRequest alloc] init] autorelease];
		NSEntityDescription *entity = [NSEntityDescription entityForName: @"ContestEntry" inManagedObjectContext: moc];
		[fetchRequest setEntity: entity];
		[fetchRequest setPredicate: predicate];
		NSArray *foundEntries = [moc executeFetchRequest: fetchRequest error: &error];
		if (!foundEntries && error)
			[NSException raise: NSGenericException format: @"%@", error];
		count = [foundEntries count];
		
		// and insert 'em
		if ( 0 != count )
		{
			[[folder mutableSetValueForKey: @"entries"] addObjectsFromArray: foundEntries];
			if ([[[folder entity] managedObjectClassName] isEqualToString: @"SharedFolder"])
				[moc markObjectAsDirty: folder];
		}
		return YES;
	}
	return NO;
}

@end
